package com.dataseek.iot.device.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dataseek.iot.device.entity.DayLevel;
import com.dataseek.iot.device.entity.DeviceAttrValueHis;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;


@Mapper
public interface DeviceAttValueHisMapper extends BaseMapper<DeviceAttrValueHis> {

    //每5分钟取一次液位数据
    @Select("<script>" +
            "select b.device_code,b.att_value as level,\n" +
            "(select c.jar_code from liquid_jar c where c.device_code=b.device_code limit 1) jarCode \n" +
            "from device_att_value_his b , \n" +
            "     (select a.device_code,max(a.create_time) create_time\n" +
            "      from device_att_value_his a\n" +
            "      where 1=1\n" +
            "      and a.att_code='liquidLevel'\n" +
            "      and a.create_time >=now()::timestamp + '-5 min'\n" +
            "      group by a.device_code\n" +
            "      ) t \n" +
            "where 1=1\n" +
            "and b.att_code='liquidLevel'\n" +
            "and b.device_code=t.device_code and b.create_time=t.create_time\n" +
            "</script>"
    )
    List<DayLevel> query5CalValue();

    //直接取5分钟一次的上报数据，不做计算直接给前台
    @Select("<script>" +
            "select a.device_code,a.att_value as level,a.create_time,\n" +
            "(select b.jar_code from liquid_jar b where b.device_code=a.device_code limit 1) jarCode\n" +
            "from device_att_value_his a \n" +
            "where 1=1\n" +
            "and a.att_code='liquidLevel'\n" +
            "and a.create_time >=CURRENT_DATE\n" +
            "        <if test=\"jarCode!=null and jarCode != ''\">" +
            "            and exists(select 1 from liquid_jar c where c.device_code=a.device_code and c.jar_code=#{jarCode})\n" +
            "        </if>" +
            "        <if test=\"deviceCode!=null and deviceCode != ''\">" +
            "            and a.device_code=#{deviceCode}\n" +
            "        </if>" +
            "order by a.create_time asc" +
            "</script>"
    )
    List<DayLevel> query5CalValueForChange(@Param("jarCode") String jarCode, @Param("deviceCode") String deviceCode);

    //带fromDate和toDate的液位原始数据查询
    @Select("<script>" +
            "select a.device_code,a.att_value as level,a.create_time,\n" +
            "(select b.jar_code from liquid_jar b where b.device_code=a.device_code limit 1) jarCode\n" +
            "from device_att_value_his a \n" +
            "where 1=1\n" +
            "and a.att_code='liquidLevel'\n" +
            "        <if test=\"fromDate==null and toDate==null\">" +
            "            and a.create_time &gt;=CURRENT_DATE\n" +
            "        </if>" +
            "        <if test=\"fromDate!=null\">" +
            "            and a.create_time &gt;=#{fromDate}\n" +
            "        </if>" +
            "        <if test=\"toDate!=null\">" +
            "            and a.create_time &lt;=#{toDate}\n" +
            "        </if>" +
            "        <if test=\"jarCode!=null and jarCode != ''\">" +
            "            and exists(select 1 from liquid_jar c where c.device_code=a.device_code and c.jar_code=#{jarCode})\n" +
            "        </if>" +
            "        <if test=\"deviceCode!=null and deviceCode != ''\">" +
            "            and a.device_code=#{deviceCode}\n" +
            "        </if>" +
            "order by a.create_time desc" +
            "</script>"
    )
    List<DayLevel> queryDayForchange(@Param("fromDate") Date fromDate, @Param("toDate") Date toDate,
                                           @Param("jarCode") String jarCode, @Param("deviceCode") String deviceCode);

    //每1小时内最后一次的液位
    @Select("<script>" +
            "select b.device_code,b.att_value as level,\n" +
            "(select c.jar_code from liquid_jar c where c.device_code=b.device_code limit 1) jarCode \n" +
            "from device_att_value_his b ,\n" +
            "     (select a.device_code,max(a.create_time) create_time\n" +
            "      from device_att_value_his a \n" +
            "      where 1=1\n" +
            "      and a.att_code='liquidLevel'\n" +
            "      and a.create_time >=now()::timestamp + '-1 hour'\n" +
            "      group by a.device_code\n" +
            "      ) t \n" +
            "where 1=1\n" +
            "and b.att_code='liquidLevel'\n" +
            "and b.device_code=t.device_code and b.create_time=t.create_time\n" +
            "</script>"
    )
    List<DayLevel> query1HourCalValue();


}
